﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using VSDBM.Code.dto;
using System.Data;
using MySql.Data.MySqlClient;

namespace VSDBM.Code.dao
{
    public class MappingDAO
    {

        public static String add(Mapping map)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();

                
                    MySqlCommand query = new MySqlCommand("INSERT INTO sequence_map (idsequence, idfeature, qualifier, posinitial, posfinal) VALUES (" + map.idsequence + "," + map.idfeature + ",'"+map.qualifier+"',"+map.posinitial+","+map.posfinal+ ")", connection);
                    query.ExecuteNonQuery();
                    SequenceDAO.setMapped(map.idsequence);
                    return "Added";
            }
            catch (Exception e)
            {
                return "ERRO! - " + e.Message;
            }
        }

        public static void delete(Mapping map)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlCommand query = new MySqlCommand("DELETE FROM sequence_map WHERE id = " + map.id + "", connection);
                query.ExecuteNonQuery();
                SequenceDAO.setUnMapped(map.idsequence);
                return;
            }
            catch (Exception e)
            {
                throw e;
               
            }
        }


        public static DataSet select(String filters, String orderby)
        {

            DataSet dSet = new DataSet();
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                adapter.SelectCommand = new MySqlCommand("SELECT * FROM sequence_map " + filters + " " + orderby, connection);
                adapter.Fill(dSet);
                return dSet;
            }
            catch (Exception e)
            {
                throw e;
                
            }
        }

        public static Mapping Fill(int id, int idsequence, int idfeature, string qualifier, int posinitial, int posfinal)
        {
            Mapping map = new Mapping(id, idsequence, idfeature, qualifier, posinitial, posfinal);
            return map;
        }

        public static List<Mapping> DStoList(DataSet ds)
        {
            List<Mapping> list = new List<Mapping>();
            try
            {
                for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    DataTable registro = ds.Tables[0];
                    list.Add(Fill(int.Parse(registro.Rows[i]["id"].ToString()), int.Parse(registro.Rows[i]["idsequence"].ToString()), int.Parse(registro.Rows[i]["idfeature"].ToString()), registro.Rows[i]["qualifier"].ToString(), int.Parse(registro.Rows[i]["posinitial"].ToString()), int.Parse(registro.Rows[i]["posfinal"].ToString())));
                }

            }
            catch (Exception e)
            {
                throw e;
            }
            return list;
        }

      
    }
}